package com.lpit.mapper;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.lpit.dto.BarChartDto;
import com.lpit.dto.UserDto;
import com.lpit.pojo.User;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @author Administrator
 * @description 针对表【user(用户表)】的数据库操作Mapper
 * @createDate 2023-06-08 11:06:01
 * @Entity com.lpit.pojo.User
 */
@Mapper
public interface UserMapper extends BaseMapper<User> {

    /**
     * 查看所有用户
     */
    public IPage<UserDto> lookUsers(@Param(Constants.WRAPPER) LambdaQueryWrapper<UserDto> wrapper, IPage<UserDto> page);

    /*查询 用户是否存在 ，返回的是用户账号 */
    String selectUserSelective(String name);

    /*查询 用户填写的原密码是否为正确，返回的是 当前用户的账号*/
    String selectUserReviseAdmin(String name, String password);

    /*用户密码修改语句*/
    void updateUserReviseAdmin(String revisePassword, String name);

    /**
     * 查询当日活跃用户
     *
     * @return List<String>
     * @author yao chen
     */
    @Select("SELECT count(*) FROM `user` WHERE DATE_FORMAT(last_time,'%Y-%m-%d')= Date(NOW())")
    public String selectUserLoginTime();

    /**
     * 查询日均活跃用户
     *
     * @author yao chen
     * @retunt List
     */
    @Select("SELECT DATE_FORMAT(last_time, '%Y-%m-%d') as date,COUNT(last_time) as number FROM `user` GROUP BY DATE_FORMAT(last_time, '%Y-%m-%d')")
    public List<BarChartDto> selectAverageDailyActiveUsers();

    /**
     * @return String
     * <span>当日新增用户<span/>
     * @author yao chen
     */
    @Select("SELECT count(*) FROM `user` WHERE DATE_FORMAT(create_time,'%Y-%m-%d')= Date(NOW())")
    public String selectNewUsers();

    /**
     * 查询日均新增用户
     *
     * @author yao chen
     * @retunt List
     */
    @Select("SELECT DATE_FORMAT(create_time, '%Y-%m-%d') as date,COUNT(create_time) as number FROM `user` GROUP BY DATE_FORMAT(create_time, '%Y-%m-%d')")
    public List<BarChartDto> selectAverageNewUserPerDay();

    /**
     * <span>查询某段时间活跃用户<span/>
     *
     * @return String
     * <span>一个月<span/>
     * @author yao chen
     */
    @Select("SELECT DATE_FORMAT(last_time, '%d') as date,COUNT(*) as number" +
            " FROM `user`" +
            "WHERE DATE_FORMAT(last_time,'%Y-%m-%d')>=DATE_FORMAT(#{kDate},'%Y-%m-%d') and DATE_FORMAT(last_time,'%Y-%m-%d') <= DATE_FORMAT(#{jDate},'%Y-%m-%d') " +
            "GROUP BY DATE_FORMAT(last_time, '%d')")
    public List<BarChartDto> queryTheActiveUsersSpecifiedPeriodOfTime(String kDate, String jDate);

    /**
     * <span>查询某段时间新增用户<span/>
     *
     * @return String
     * <span>一个月<span/>
     * @author yao chen
     */
    @Select("SELECT DATE_FORMAT(create_time, '%d') as date,COUNT(*) as number" +
            " FROM `user`" +
            "WHERE DATE_FORMAT(create_time,'%Y-%m-%d')>=DATE_FORMAT(#{kDate},'%Y-%m-%d') and DATE_FORMAT(create_time,'%Y-%m-%d') <= DATE_FORMAT(#{jDate},'%Y-%m-%d') " +
            "GROUP BY DATE_FORMAT(create_time, '%d')")
    public List<BarChartDto> userAddedWithinSpecifiedPeriod(String kDate, String jDate);

}




